#!/usr/bin/perl
#
# Database backend for CGCC data gathering plugin
# action=save data=roid,Edras,G-11,1,2,3\nroid,...
# action=get system=Edras sector=G-11
#
use strict;
use warnings;
use CGI;
use Data::Dumper;
use DBI;
use FindBin;
use Try::Tiny;

my $c = new CGI;

# Configuration section
our $debug = 0;
our $dsn;
our $db_user_name;
our $db_password;
my $db = "$FindBin::Bin/db.txt";
do $FindBin::Bin."/db.conf";

# TODO check passcode

my $dbh = setup_db($dsn, $db_user_name, $db_password);

if ($c->param("action") eq "get")
{
    my $o = getdata($c, $dbh);
    if (defined $o)
    {
	print $c->header(-status => 200, -type => "text/plain");
	print @$o;
    }
    else
    {
	print $c->header(-status => 501, -type => "text/plain");
	print "msg,something went wrong";
    }
}
elsif ($c->param("action") eq "save")
{
    my $s = savedata($c, $dbh);
    if (defined $s)
    {
	print $c->header(-status => 200, -type => "text/plain");
	print "msg,data saved\n";
    }
    else
    {
	print $c->header(-status => 501, -type => "text/plain");
	print "msg,something went wrong";
    }
}
else
{
    print $c->header();
    print $c->start_html('hello world'),
    $c->h1('hello world'),
    $c->Dump(),
    $c->end_html;

}
    open(my $f, ">> /tmp/out") || die $!;
    print $f scalar(localtime(time)),"\n";
    print $f Dumper($c);
    close $f;

$dbh->disconnect();
exit 0;

sub savedata
{
    my $c = shift;
    my $dbh = shift;
    my $sth = {
	"roid" => {
	    "select" => $dbh->prepare("SELECT * FROM cgcc_roids WHERE system = ? AND sector = ? AND id = ?"),
	    "update" => $dbh->prepare("UPDATE cgcc_roids SET upd=?, who=?, cooldown=?, temp=?, info=? WHERE system = ? AND sector = ? AND id = ?"),
	    "insert" => $dbh->prepare("INSERT INTO cgcc_roids VALUES(?,?,?,?,?,?,?,?)"),
	}
    };
    foreach my $d (split("\n", $c->param("data")))
    {
	my @d = csvsplit($d);
	my $kind = shift @d;
	
	try
	{
	    
	$sth->{$kind}{select}->execute($d[0], $d[1], $d[2]);
	if (my @curd = $sth->{$kind}{select}->fetchrow_array())
	{
	    $sth->{$kind}{update}->execute(@d[3,4,5,6,7, 0,1,2]);
	}
	else
	{
	    $sth->{$kind}{insert}->execute(@d);
	}
            $dbh->commit();
	}
	catch
	{
            warn "Error: unable save data $d: $DBI::errstr $@\n";
            $dbh->rollback();
	}
    }
}
sub getdata
{
    my $c = shift;
    my $dbh = shift;
    my $sys = $c->param("system");
    my $sect = $c->param("sector");
    my @out = ();

    foreach my $table (qw(cgcc_roids))
    {
	my $query = "SELECT * FROM $table";
	my @params = ();
	if ($sys)
	{
	    $query .= " WHERE system = ?";
	    push @params, $sys;
	    if ($sect)
	    {
		$query .= " AND sector = ?";
		push @params, $sect;
	    }
	}
	my $sth = $dbh->prepare($query);	    
	$sth->execute(@params);
	while(my @f = $sth->fetchrow_array())
	{
	    push @out, csvjoin("roids", @f),"\n";
	}
    }
    return \@out;
}

# ------------------------------------------------------------------------
# CSV
# ------------------------------------------------------------------------
# TODO  these are simplistic placeholders... replace!
sub csvsplit
{
    my $str = shift;
    # split will drop trailing blank sections... add a space to workaround
    my @a = split(",", $str." ");
    chop $a[$#a];
    return @a;
}

sub csvjoin
{
    return join(",", @_);
}

# ------------------------------------------------------------------------
# DB Utilities
# ------------------------------------------------------------------------
# this is the schema for the db, as the db is updated, new entries
# should be added to the array.  These will be applied next time this
# script is run
sub schema
{
    return [
        ["unused"],
        ["CREATE TABLE cgcc_config (name VARCHAR(80), value VARCHAR(1024))",
         "CREATE TABLE cgcc_sectorinfo (system VARCHAR(30), sector VARCHAR(5))",
         "CREATE TABLE cgcc_roids (system VARCHAR(30), sector VARCHAR(5), id INT, upd INT, who VARCHAR(30), cooldown FLOAT, temp FLOAT, info VARCHAR(256))",
#Aquean Silicate Carbonic Ferric VanAzek Ishik Xithricite Lanthanic Denic Pyronic Apicene Pentric Heliocene
         "INSERT INTO  cgcc_config VALUES ('schema', '1')"],
#        ["ALTER TABLE task_info ADD (parent INTEGER)"],
        ];
}
# setup the DB connection and make sure the DB is up-to-date
sub setup_db
{
    my $dsn = shift;
    my $user = shift;
    my $passwd = shift;

    my $dbh = DBI->connect($dsn, $user, $passwd,
                           { AutoCommit => 0, RaiseError => 1, PrintError => 0, ChopBlanks => 1});
    die "Error: unable to connect to server $DBI::errstr" unless $dbh;

    # get the db schema
    my $dbschema = getdbvar($dbh, "schema") || 0;

    my $s = schema();
    return $dbh if $dbschema == $#$s;

    # run updates to latest schema level
    foreach my $i ($dbschema+1..$#$s)
    {
        try
        {
            print "upgrade needed to schema level $i\n" if $debug;
            foreach my $us (@{$s->[$i]})
            {
                print "\t$us\n" if $debug;
                $dbh->do($us) || die "Error: unable to run $us $DBI::errstr";
            }
	    setdbvar($dbh, "schema", $#$s);
            $dbh->commit();
        }
        catch
        {
            warn "Error: unable to upgrade to schema $i: $DBI::errstr\n";
            $dbh->rollback();
	    $dbh = undef;
        }
    }
    return $dbh;
}

# ------------------------------------------------------------------------
# these routines are for settings stored in the database
#
sub getdbvar
{
    my $dbh = shift;
    my $var = shift;
    my $ret;

    # for some reason try/catch won't work here,
    # do it the old-fashioned way
    my $sth = $dbh->prepare("SELECT value FROM cgcc_config WHERE name = ?");
    return undef unless $sth;  # no such table?
    $sth->execute($var) || warn "Error: fetching var $var: $DBI::errstr";
    ($ret) = $sth->fetchrow_array();
    print "Getting $var -> $ret\n" if $debug;
    return $ret;
}

sub setdbvar
{
    my $dbh = shift;
    my $var = shift;
    my $value = shift;
    print "setting $var to $value\n" if $debug;
    my $sth = $dbh->prepare("UPDATE cgcc_config SET value = ? WHERE name = ?");
    $sth->execute($value, $var) || warn "Error: setting var $var: $DBI::errstr";
    $dbh->commit();
}

